package excel;

import date.UtilDate;
import org.apache.poi.ss.usermodel.*;

import java.util.Date;

/**
 * Description: get or set cell value
 * Created by 4790K_NSL on 2017/4/28.
 */
public class UtilExcelCellValue {

    private static String defaultDatePattern = "yyyy-MM-dd";


    /** 得到表格内容，返回类型可为多种Cell格式。
     * @param cell
     * @return
     */
    static public Object getCellValue(Cell cell){
        Object value=null;
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //System.out.println(cell.getRichStringCellValue().getString());
                value=cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //System.out.println(cell.getDateCellValue());
                    value=cell.getDateCellValue();
                } else {
                    //System.out.println(cell.getNumericCellValue());
                    value=cell.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.println(cell.getBooleanCellValue());
                value=cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                //System.out.println(cell.getCellFormula());
                value=cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK:
                //System.out.println();
                value="";
                break;
            default:
                value="";
        }
        return  value;
    }

    /**
     * 将单元格内容以字符串的形式返回
     * @param cell
     * @return
     */
   static public String getCellValueAsString(Cell cell){
       if (cell == null) {
           return null;
       }
       if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)){
           Date date=cell.getDateCellValue();
           return UtilDate.format(date,defaultDatePattern);
       }

       if(cell.getCellType()==Cell.CELL_TYPE_FORMULA){
           String value=null;
           try {
               value = String.valueOf(cell.getNumericCellValue());
           } catch (IllegalStateException e) {
               value = String.valueOf(cell.getRichStringCellValue());
            }
            return value;
       }

       DataFormatter formatter = new DataFormatter();
       String text = formatter.formatCellValue(cell);
       return text;
   }


    /**
     * 返回指定行x,列y 处的表格内容，以字符串形式返回
     * @param sheet
     * @param rowNumber
     * @param columnNumber
     * @return
     */
    static public String getCellValueAsStringByAddress(Sheet sheet , int rowNumber, int columnNumber) {
        Row row=sheet.getRow(rowNumber);
        Cell cell=row.getCell(columnNumber );
        return getCellValueAsString(cell);
    }

    /**根据point，获取sheet中的指定位置的字符串形式取值
     * @param sheet
     * @param addressString     eg. A3,BB5
     * @return
     */
    static public String getCellValueAsStringByAddress(Sheet sheet , String addressString) {
        ExcelPoint point= UtilExcelCoordinateCellAddress.convertFromCellAddress(addressString);
        return getCellValueAsStringByAddress(sheet,point);
    }

    /**根据point，获取sheet中的指定位置的字符串形式取值
     * @param sheet
     * @param point
     * @return
     */
    static public String getCellValueAsStringByAddress(Sheet sheet , ExcelPoint point) {
        return getCellValueAsStringByAddress(sheet,point.getX(),point.getY());
    }


    /** 返回指定地址处的Cell Value, 以String形式返回
     * @param workbook
     * @param sheetName
     * @param addressString eg. A3,AB41
     * @return
     */
    static public String getCellValueAsStringByAddress(Workbook workbook, String sheetName , String addressString) {
        ExcelPoint point=UtilExcelCoordinateCellAddress.convertFromCellAddress(addressString);
        return getCellValueAsStringByAddress(workbook,sheetName,point);
    }


    /**
     * 返回指定行x,列y 处的表格内容，以字符串形式返回
     * @param workbook
     * @param sheetName
     * @param rowNumber
     * @param columnNumber
     * @return
     */
    static public String getCellValueAsStringByAddress(Workbook workbook, String sheetName , int rowNumber, int columnNumber) {
       Sheet sheet=workbook.getSheet(sheetName);
       Row row=sheet.getRow(rowNumber);
        Cell cell=row.getCell(columnNumber );
        return getCellValueAsString(cell);
    }

    /**
     * 返回指定行x,列y 处的表格内容，以字符串形式返回
     * @param workbook
     * @param sheetName
     * @param point
     * @return
     */
    static public String getCellValueAsStringByAddress(Workbook workbook, String sheetName , ExcelPoint point) {
        Sheet sheet=workbook.getSheet(sheetName);
        return getCellValueAsStringByAddress(sheet,point);
    }

    /**返回指定行x,列y 处的表格内容，以字符串形式返回
     * @param sheet
     * @param rowNumber
     * @param columnNumber
     * @return
     */
    static public Object getCellValueByAddress(Sheet sheet , int rowNumber, int columnNumber) {
        Row row=sheet.getRow(rowNumber);
        Cell cell=row.getCell(columnNumber );
        return getCellValue(cell);
    }

}
